<?php

namespace MLM\Model\Sn;

class Storage extends \MLM\Model\Generic\Db\Storage {


    public function getSNConfig($key){

        $sql = "select *
                from sysconfig
                where sysconfig_name = ?";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));

        return $res;
    }


    private function isExistSn($sn){
        $sql = "select count(*) cnt from sn where sn_code = ?";

        $stmt = $this->_db->query($sql);
        $getCnt = $stmt->execute(array($key));

        $cnt = 0;
        while($getCnt->next()){
                $res = $getCnt->current();
                $cnt = $res['cnt'];
        }

        if($cnt > 0 ){
            return true;
        }else{
            return false;
        }

    }

    public function genSN($prefix, $len){
        
        $snModel = new \MLM\Model\Sn;

        //$gen = $snModel->getRandom($len);
        $sql = "select int_value+1 as seq from sysconfig where sysconfig_name = 'SN_SEQ'";

        $stmt = $this->_db->query($sql);
        $getSeq = $stmt->execute(array($key));

        while($getSeq->next()){
                $res = $getSeq->current();
                $seq = $res['seq'];
        }

        $gen = str_pad($seq, $len-1, "0", STR_PAD_LEFT);

        if ($gen == ""){
                $this->genSN($prefix, $len);
        }else{
            //$sn = $gen.$sum;
            $sn = $prefix.$gen;

            // comment for testing, DB not exist
            if(!$this->isExistSn($sn)){
 
               $pin = $snModel->generatePIN($len);

               $insert = $this->_sql->insert()
                    ->into('sn')
                    ->values(array(
                        'sn_code'=> $sn,
                        'sn_pin' => $pin,
                        'sn_status' => 0
                        ));

               $this->execute($insert);

               //Update seq
               $update = $this->_sql->update()
                    ->table('sysconfig')
                    ->set(array('int_value' => $seq))
                    ->where(array('sysconfig_name' => 'SN_SEQ'));

               $this->execute($update);


               //$this->_db->insert('sn',array('sn_code'=>$sn, 'sn_pin' => $pin,'sn_status'=> -2));
               return $sn.','.$pin;
               //echo ($i+1).". XF".$sn->genSN() ."<br>";
            }else{
               $this->genSN($prefix, $len);
            }

        }
    }


    public function getSN($num, $sn, $stts, $start, $limit, $search = "", $searchField = ""){

        if ($num != ""){
            if ($limit != ""){

                $sql = "select count(1) as total
                        from sn where sn_status = 0 ";

                if($search == 'true'){
                    $sql .= ' and '.$searchField;
                }

                $stmt = $this->_db->query($sql);
                $getTotal = $stmt->execute(array($key));

                while($getTotal->next()){
                        $res = $getTotal->current();
                        $rows = $res['total'];
                }

                $data = array($rows);


            }

            $sql = "select sn_code as serial_number, sn_pin as pin, 'Baru' as status
                    from sn
                    where sn_status = 0 ";

            if($search == 'true'){
                $sql .= ' and '.$searchField;
            }

            $sql .= " order by sn_code ";

            if ($limit != ""){
                $sql .= " limit $start, $limit";
            }



        }else{

            if ($limit != ""){

                $sql = "select count(1) as total
                        from sn where /*status = -1
                        and*/ sn_code like '%$sn%'";

                if ($stts != ""){
                     $sql .= " and sn_status = $stts";
                }else{
                    $sql .= " and sn_status > 0 ";
                }

                if($search == 'true'){
                    $sql .= ' and '.$searchField;
                }

                $stmt = $this->_db->query($sql);
                $getTotal = $stmt->execute(array($key));

                while($getTotal->next()){
                        $res = $getTotal->current();
                        $rows = $res['total'];
                }

                $data = array($rows);
            }

            $sql = "select sn_code as serial_number, sn_pin as pin, if(sn_status < 2, 'Pasif/Tidak Aktif', if(sn_status = 2, 'Aktif', 'Sudah Digunakan')) as status
                    from sn
                    where sn_code like '%$sn%' ";

            if ($stts != ""){
                 $sql .= " and sn_status = $stts";
            }else{
                $sql .= " and sn_status > 0 ";
            }

            if($search == 'true'){
                $sql .= ' and '.$searchField;
            }

            $sql .= " order by sn_code ";

            if ($limit != ""){
                $sql .= " limit $start, $limit";
            }

        }

        

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($key));

        $dataSN = array();
        while($result->next()){
                $res = $result->current();
                array_push($dataSN, array("SN" => $res['serial_number'], "PIN" => $res['pin'], "Status" => $res['status']));
        }


        array_push($data, $dataSN);

        if ($limit != ""){
            array_push($data, $dataSN);
        }else{
            $data = $dataSN;
        }

        return $data;
    }


    public function updateStatusSN($sn, $stts){
        
        $result = array();

        /*
         * Update Status SN Bulk 
         */
        $sql = "update sn set sn_status = ? where sn_code in ($sn)";
        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($stts));

        return $result;
    }



    public function updateSn($data, $where){

        $sql = $this->update()
                    ->table('sn')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;
    }
    

    public function aktivasiSN($snList, $flag, $sn) {


        if ($flag == 1){ //Aktifkan yang dipilih
            $sql = "update sn set sn_status = 2 where sn_code in ($snList) and sn_status = 1";
        }else if ($flag == 2){ //Aktifkan semua
            $sql = "update sn set sn_status = 2 where sn_code like '%$sn%' and sn_status = 1";
        }else if ($flag == 3){  //Pasifkan semua
            $sql = "update sn set sn_status = 1 where sn_code like '%$sn%' and sn_status = 2";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        return $result;
    }

    
    
}
